import datetime
import lib.mysql as mysql
import lib.config as config
import lib.util as util

# 已拆红包奖励
def get_red_packet_opened():
    sql = "SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS dd, SUM(amount) FROM driver_reward WHERE category = 2 AND is_verified = 1 AND created_at BETWEEN '{}' AND '{}' GROUP BY dd".format(start_time, end_time)
    return db.getRows(sql)

# 未拆红包
def get_red_packet_unopened():
    sql = "SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS dd, SUM(amount) FROM driver_reward WHERE category = 2 AND is_verified = 0 AND created_at BETWEEN '{}' AND '{}' GROUP BY dd".format(start_time, end_time)
    return db.getRows(sql)

# 高峰接单奖励
def get_peak_amount():
    sql = "SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS dd, SUM(amount) FROM driver_reward WHERE category = 1 AND created_at BETWEEN '{}' AND '{}' GROUP BY dd".format(start_time, end_time)
    return db.getRows(sql)

# 高峰接单奖励数量
def get_peak_count():
    sql = "SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS dd, count(1) FROM driver_reward WHERE category = 1 AND created_at BETWEEN '{}' AND '{}' GROUP BY dd".format(start_time, end_time)
    return db.getRows(sql)


# 推广奖励
def get_recommend_amount():
    sql = "SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS dd, SUM(amount) FROM driver_reward WHERE category in (3, 4) AND created_at BETWEEN '{}' AND '{}' GROUP BY dd".format(start_time, end_time)
    return db.getRows(sql)


# 推广奖励-端
def get_situation():
    sql = "SELECT DATE_FORMAT(driver_reward.created_at, '%Y-%m-%d') AS dd,driver_marketing.situation, SUM(driver_reward.amount) AS amount from driver_reward LEFT JOIN driver_marketing ON driver_reward.marketing_id = driver_marketing.id WHERE driver_reward.created_at BETWEEN '{}' AND '{}' GROUP BY driver_marketing.situation, dd".format(start_time, end_time)
    return db.getRows(sql)


# 推广人数 recommend_count
def get_recommend_count():
    sql = "SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS dd, COUNT(1) FROM recommendation WHERE promote_channel = 0 AND created_at BETWEEN '{}' AND '{}' GROUP BY dd".format(start_time, end_time)
    return db.getRows(sql)


# app 人数 recommend_app_count
def get_recommend_app_count():
    sql = "SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS dd, COUNT(1) FROM recommendation WHERE promote_channel = 0 and promote_platform = 1 AND created_at BETWEEN '{}' AND '{}' GROUP BY dd".format(start_time, end_time)
    return db.getRows(sql)


# 小程序人数 recommend_wxmina_count
def get_recommend_wxmina_count():
    sql = "SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS dd, COUNT(1) FROM recommendation WHERE promote_channel = 0 and promote_platform = 0 AND created_at BETWEEN '{}' AND '{}' GROUP BY dd".format(start_time, end_time)
    return db.getRows(sql)


# 订单数
def get_order_count():
    sql = "SELECT DATE_FORMAT(last_pay_time, '%Y-%m-%d') AS dd, COUNT(1) FROM ride_order WHERE state = 2 AND refunded = 0 AND last_pay_time BETWEEN '{}' AND '{}' GROUP BY dd".format(start_time, end_time)
    return db.getRows(sql)


def getMainData():
    red_packet_opened_list = get_red_packet_opened()
    red_packet_opened_map = {a[0]: a[1] for a in red_packet_opened_list}
    red_packet_unopened_list = get_red_packet_unopened()
    red_packet_unopened_map = {a[0]: a[1] for a in red_packet_unopened_list}
    peak_amount_list = get_peak_amount()
    peak_amount_map = {a[0]: a[1] for a in peak_amount_list}
    peak_count_list = get_peak_count()
    peak_count_map = {a[0]: a[1] for a in peak_count_list}
    recommend_amount_list = get_recommend_amount()
    recommend_amount_map = {a[0]: a[1] for a in recommend_amount_list}
    situation = get_situation()
    situation_map = {f"{a[0]}-{a[1]}": a[2] for a in situation}
    # recommend_app_amount in 4,5,6
    # recommend_wxmina_amount in 3,7
    recommend_count_list = get_recommend_count()
    recommend_count_map = {a[0]: a[1] for a in recommend_count_list}
    recommend_app_count_list = get_recommend_app_count()
    recommend_app_count_map = {a[0]: a[1] for a in recommend_app_count_list}
    recommend_wxmina_count_list = get_recommend_wxmina_count()
    recommend_wxmina_count_map = {a[0]: a[1] for a in recommend_wxmina_count_list}
    order_count_list = get_order_count()
    order_count_map = {a[0]: a[1] for a in order_count_list}

    # red_packet_opened, red_packet_unopened, peak_amount, peak_count, recommend_amount, recommend_app_amount, recommend_wxmina_amount, recommend_count, recommend_app_count, recommend_wxmina_count, order_count
    start = datetime.datetime.strptime(start_time, '%Y-%m-%d')
    end = datetime.datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S')
    insert_data = []
    while(start < end):
        date = start.strftime('%Y-%m-%d')
        item = {
            'stats_date': date,
            'red_packet_opened': red_packet_opened_map.get(date, 0),
            'red_packet_unopened': red_packet_unopened_map.get(date, 0),
            'peak_amount': peak_amount_map.get(date, 0),
            'peak_count': peak_count_map.get(date, 0),
            'recommend_amount': recommend_amount_map.get(date, 0),
            'recommend_app_amount': situation_map.get(f"{date}-4", 0) + situation_map.get(f"{date}-5", 0) + situation_map.get(f"{date}-6", 0),
            'recommend_wxmina_amount': situation_map.get(f"{date}-3", 0) + situation_map.get(f"{date}-7", 0),
            'recommend_count': recommend_count_map.get(date, 0),
            'recommend_app_count': recommend_app_count_map.get(date, 0),
            'recommend_wxmina_count': recommend_wxmina_count_map.get(date, 0),
            'order_count': order_count_map.get(date, 0)
        }
        insert_data.append(item)
        start = start + datetime.timedelta(days=1)
    return insert_data


start_time = util.get_start_date()
end_time = util.get_end_date()

current_time = datetime.datetime.now()
if end_time is None:
    # 转为指定格式
    end_time = current_time.strftime("%Y-%m-%d 23:59:59")
else:
    end_time = f"{end_time} 23:59:59"

if start_time is None:
    start_time = datetime.datetime.strptime(end_time, "%Y-%m-%d %H:%M:%S") - datetime.timedelta(days=3)
    start_time = start_time.strftime("%Y-%m-%d")
current_time = current_time.strftime("%Y-%m-%d %H:%M:%S")

util.add_log(f"即将处理 {start_time} - {end_time} 的司机补贴数据")

db = mysql.Db(config.config['mysql']['master'])
db1 = mysql.Db(config.config['mysql']['slave'])

call_data = getMainData()
call_size = len(call_data)
util.add_log(f"共有数据 {call_size}")
db1.cursor.execute("START TRANSACTION")
try:
    sql = "delete from cache_allowance_driver where (stats_date BETWEEN '{}' AND '{}')".format(start_time, end_time)
    db1.cursor.execute(sql)
    if call_size > 0:
        sql = "INSERT INTO `cache_allowance_driver` (`stats_date`, `red_packet_opened`, `red_packet_unopened`, `peak_amount`, `peak_count`, `recommend_amount`, `recommend_count`, `recommend_app_amount`, `recommend_app_count`, `recommend_wxmina_amount`, `recommend_wxmina_count`, `order_count`, `created_at`, `updated_at`) VALUES "
        for item in call_data:
            sql += f"('{item['stats_date']}', {item['red_packet_opened']}, '{item['red_packet_unopened']}', {item['peak_amount']}, {item['peak_count']}, {item['recommend_amount']}, {item['recommend_count']}, {item['recommend_app_amount']}, {item['recommend_app_count']},{item['recommend_wxmina_amount']}, {item['recommend_wxmina_count']},{item['order_count']}, '{current_time}', '{current_time}'),"
        sql = sql.strip(',')
        db1.cursor.execute(sql)
    db1.conn.commit()
    util.add_log("处理完成")
except Exception as e:
    db1.conn.rollback()
    util.add_log(f"处理失败: {e}")
